<?php
require('functions/page_load.php');
require_once("../classes/class.phpmailer.php");

$jobs_nav = true;

// ban

if(isset($_GET['ban'])){

	$sql = "UPDATE users SET banned = 1 WHERE id = '".mysql_real_escape_string($_GET['ban'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	
	$sql = "DELETE FROM jobs WHERE id = '".mysql_real_escape_string($_GET['job_id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$results = '<div class="success_result">Job deleted and user banned successfully.</div>';

}

// deactivate

if($_GET['action'] == 'deactivate_job' && isset($_GET['id'])){

	$sql = "UPDATE jobs SET status = 'Closed', job_active = '0' WHERE id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$results = '<div class="success_result">Job deactivated successfully.</div>';

}

// deletes

if(isset($_GET['delete_job'])){

	$sql = "UPDATE jobs SET deleted = 1 WHERE id = '".mysql_real_escape_string($_GET['delete_job'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$results = '<div class="success_result">Job deleted successfully.</div>';

}

// approves

if(isset($_GET['id']) && $_GET['action'] == 'activate_job'){

$tradesmen = array();

// activate job

	$sql = "UPDATE jobs SET status = 'Open', job_active = '1' WHERE id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

// find approved tradesman

	$sql = "SELECT jobs.custom, jobs.area_id, jobs.trade_id, areas.area, trades.trade, jobs.title, users.email, users.firstname, users.surname, users.approvals, addresses.latitude, addresses.longitude, jobs.description FROM jobs 
	LEFT JOIN areas ON jobs.area_id = areas.id 
	LEFT JOIN trades ON jobs.trade_id = trades.id 
	LEFT JOIN users ON jobs.user_id = users.id 
	LEFT JOIN addresses ON jobs.address_id = addresses.id 
	WHERE jobs.id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$job_rs=mysql_fetch_assoc($query);

	$job_link = build_job_link($job_rs['trade'],$job_rs['area'],$_GET['id']);

if($job_rs['approvals'] == '1'){

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = "mail@iwantatradesman.co.uk";
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($job_rs['email']);

	$mail->Subject = "Job notification";
	$mail->Body = "Dear ".$job_rs['firstname']." ".$job_rs['surname']."\r\n\r\nCongratulations! Your job is now activated and awaiting quotation from local tradesmen.\r\n\r\nJob title: ".ucfirst($job_rs['title'])."\r\n\r\nYou can view the job here: http://www.iwantatradesman.co.uk".$job_link."\r\n\r\nKind regards\r\n\r\nThe I Want A Tradesman Team\r\n\r\n\r\nThis email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.";

	$mail->Send();
}

	if($job_rs['custom'] == '1'){

	$sql = "SELECT tradesman.id, email FROM tradesman 
	LEFT JOIN job_to_tradesman ON tradesman.id = job_to_tradesman.tradesman_id 
	LEFT JOIN users ON tradesman.user_id = users.id 
	WHERE job_to_tradesman.job_id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	while($rs=mysql_fetch_assoc($query)){

	$tradesmen[$rs['id']] = $rs['email'];

	}

	}

	else{
	
	// find tradesmen within radius
	//Change By Vyas Ishan Added the the field status condition status = '1'	24 May 2012
	$sql = "SELECT tradesman.id, email, ROUND(SQRT(POW((69.1 * (".$job_rs['latitude']." - latitude)), 2) + POW((53 * (".$job_rs['longitude']." - longitude)), 2)), 1) AS distance, radius FROM tradesman 
			LEFT JOIN addresses ON tradesman.address_id = addresses.id 
			LEFT JOIN tradesman_to_trade ON tradesman_to_trade.tradesman_id = tradesman.id 
			LEFT JOIN users ON tradesman.user_id = users.id 
			LEFT JOIN subscriptions ON tradesman.subscription_id = subscriptions.id 
			WHERE tradesman_to_trade.trade_id = '".$job_rs['trade_id']."' AND tradesman.active = '1' AND subscriptions.status = '1' 
			GROUP BY tradesman.id 
			HAVING distance <= subscriptions.radius";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	while($rs=mysql_fetch_assoc($query)){
	$tradesmen[$rs['id']] = $rs['email'];
	}

	}

// send emails

	foreach($tradesmen AS $key => $value){

	$sql = "SELECT firstname, surname FROM tradesman 
	LEFT JOIN users ON tradesman.user_id = users.id 
	WHERE tradesman.id = '".$key."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$user_rs=mysql_fetch_assoc($query);

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = "mail@iwantatradesman.co.uk";
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($value);

	$mail->Subject = "Job notification";
	$mail->Body = "Dear ".$user_rs['firstname']." ".$user_rs['surname']."\r\n\r\nYou have a new job available to quote!\r\n\r\nJob title: ".ucfirst($job_rs['title'])."\r\nArea: ".$job_rs['area']."\r\n\r\nYou can view the job here: http://www.iwantatradesman.co.uk".$job_link."\r\n\r\nKind regards\r\n\r\nThe I Want A Tradesman Team\r\n\r\n\r\nThis email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.";

	$mail->Send();

	}

// setup sms

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = $settings['sms_email'];
	$mail->FromName = $settings['sms_email'];

	$i = 0;

	foreach($tradesmen AS $key => $value){

	// check for sms and notification

	$sql = "SELECT sms_remaining, mobile, mobile_verified, custom_quotes FROM SMS 
	LEFT JOIN tradesman ON SMS.tradesman_id = tradesman.id 
	LEFT JOIN users ON tradesman.user_id = users.id 
	WHERE tradesman.id = '".$key."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$sms_rs=mysql_fetch_assoc($query);

	if($sms_rs['sms_remaining'] > 0 && strlen($sms_rs['mobile']) > 0 && $sms_rs['mobile_verified'] == '1' && ($sms_rs['custom_quotes'] == '1' && $job_rs['custom'] == '1' || $job_rs['custom'] != '1')){

	$i++;

	$mail->AddAddress("44".substr($sms_rs['mobile'], 1)."@bulksms.co.uk");

// deduct sms

	$new_sms_remaining = $sms_rs['sms_remaining'] - 1;

	$sql = "UPDATE SMS SET sms_remaining = '".$new_sms_remaining."' WHERE tradesman_id = '".$key."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	}

	}

// SEND SMS

	if($i > 0){

	$mail->Subject = $settings['sms_password'];
	$mail->Body = "You have a new job available to quote on I Want A Tradesman. Go to your account now and provide a quotation.";

	$mail->Send();

	}
	
	// add to twitter
	require_once('twitteroauth/twitteroauth.php');

	/* Create a TwitterOauth object with consumer/user tokens. */
	$connection = new TwitterOAuth(CONSUMER_KEY, CONSUMER_SECRET, OAUTH_TOKEN, OAUTH_TOKEN_SECRET);

	/* statuses/update */
	$parameters = array('status' => ucfirst($job_rs['title']).' - '.$job_rs['area'].' - http://www.iwantatradesman.co.uk'.$job_link);
	$status = $connection->post('statuses/update', $parameters);
	
	// add to facebook
	post_to_facebook('New job! '.$job_rs['trade'].' wanted in '.$job_rs['area'], 'http://www.iwantatradesman.co.uk'.$job_link, $job_rs['description']);

$results = '<div class="success_result">Job approved successfully.</div>';
	
}

		switch($_GET['show']){
		
		case "inactive";
		$filter_text = "Jobs awaiting moderation";
		break;
		
		case "completed";
		$filter_text = "Completed Jobs";
		break;
		
		case "cancelled";
		$filter_text = "Cancelled Jobs";
		break;
		
		case "deleted";
		$filter_text = "Deleted Jobs";
		break;
		
		default:
		$filter_text = "Jobs";
		break;
		
		}


require('includes/header.php');
?>

<div class="side_container">
<h2 class="container_header">Filters</h2>

<ul>
<li><a href="jobs.php">Active jobs</a></li>
<li><a href="jobs.php?show=inactive">Jobs awaiting moderation</a></li>
<li><a href="jobs.php?show=completed">Completed jobs</a></li>
<li><a href="jobs.php?show=cancelled">Cancelled jobs</a></li>
<li><a href="jobs.php?show=deleted">Deleted jobs</a></li>
</ul>

</div>

<div class="main_container_wrapper">

	<div class="main_container">
	<h2 class="container_header"><?php echo $filter_text; ?></h2>
	
	<!--<a href="add_job.php" class="create_button">Create new job</a>-->
	
	<?php echo $results; ?>
	
	<?php
	
		switch($_GET['show']){
		
		case "inactive";
		$sql = "SELECT jobs.id, users.id AS user_id, firstname, surname, title, trade, area, job_active, date_added FROM jobs 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN users ON jobs.user_id = users.id 
		WHERE job_active = 0 AND deleted = 0 
		ORDER BY date_added DESC";
		break;
		
		case "completed";
		$sql = "SELECT jobs.id, users.id AS user_id, firstname, surname, title, trade, area, job_active, date_added, company_name, tradesman.id AS tradesman_id FROM jobs 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN users ON jobs.user_id = users.id 
		LEFT JOIN quotes ON jobs.quote_id = quotes.id 
		LEFT JOIN tradesman ON quotes.tradesman_id = tradesman.id 
		WHERE job_active = 1 AND jobs.deleted = 0 AND jobs.status = 'Closed' AND quote_id != 0 
		ORDER BY date_added DESC";
		break;
		
		case "cancelled";
		$sql = "SELECT jobs.id, users.id AS user_id, firstname, surname, title, trade, area, job_active, date_added, reason FROM jobs 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN users ON jobs.user_id = users.id 
		LEFT JOIN cancellation_reasons ON jobs.cancellation_reason = cancellation_reasons.id 
		WHERE cancellation_reason != 0 
		ORDER BY date_added DESC";
		break;
		
		case "deleted";
		$sql = "SELECT jobs.id, users.id AS user_id, firstname, surname, title, trade, area, job_active, date_added FROM jobs 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN users ON jobs.user_id = users.id 
		WHERE deleted = 1 
		ORDER BY date_added DESC";
		break;
		
		default:
		$sql = "SELECT jobs.id, users.id AS user_id, firstname, surname, title, trade, area, job_active, date_added FROM jobs 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN users ON jobs.user_id = users.id 
		WHERE job_active = 1 AND deleted = 0 
		ORDER BY date_added DESC";
		break;
		
		}

	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);
	
	if($rows > 0){
	
	echo '<table width="100%" cellpadding="0" cellspacing="0" class="cms_table paginate_table">';
	
		switch($_GET['show']){
		
		case "inactive";
		echo '<thead><tr><th align="left">Title</th><th>User</th><th>Trade</th><th>Date</th><th width="50">Approve</th><th width="50">Ban</th><th width="50">Delete</th></tr></thead>';
		break;
		
		case "completed";
		echo '<thead><tr><th align="left">Title</th><th>User</th><th>Trade</th><th>Date</th><th>Tradesman</th></thead>';
		break;
		
		case "cancelled";
		echo '<thead><tr><th align="left">Title</th><th>User</th><th>Trade</th><th>Date</th><th>Reason</th></thead>';
		break;
		
		case "deleted";
		echo '<thead><tr><th align="left">Title</th><th>User</th><th>Trade</th><th>Date</th><th width="50">Approve</th><th width="50">Ban</th></tr></thead>';
		break;
		
		default:
		echo '<thead><tr><th align="left">Title</th><th>User</th><th>Trade</th><th>Date</th><th width="50">Deactivate</th><th width="50">Ban</th></tr></thead>';
		break;
		
		}

	echo '<tbody>';
	
		while($rs=mysql_fetch_assoc($query)){
		
			switch($_GET['show']){
			
			case "inactive";
			echo '<tr><td align="left"><a href="edit_job.php?id='.$rs['id'].'">'.$rs['title'].'</a></td><td><a href="edit_user.php?id='.$rs['user_id'].'">'.$rs['firstname'].' '.$rs['surname'].'</a></td><td>'.$rs['trade'].'</td><td>'.date("d/m/Y", strtotime($rs['date_added'])).'</td><td><a href="?id='.$rs['id'].'&amp;action=activate_job">Approve</a></td><td><a onclick="confirm_ban(\'?ban='.$rs['user_id'].'&amp;job_id='.$rs['id'].'\',\'this user\');" title="Ban"><img src="images/na.png" alt="Ban" /></a></td><td><a onclick="confirm_delete(\'?delete_job='.$rs['id'].'\',\'this job\');" title="Delete"><img src="images/delete.png" alt="Delete" /></a></td></tr>';
			break;
			
			case "completed";
			echo '<tr><td align="left"><a href="edit_job.php?id='.$rs['id'].'">'.$rs['title'].'</a></td><td><a href="edit_user.php?id='.$rs['user_id'].'">'.$rs['firstname'].' '.$rs['surname'].'</a></td><td>'.$rs['trade'].'</td><td>'.date("d/m/Y", strtotime($rs['date_added'])).'</td><td><a href="edit_tradesman.php?id='.$rs['tradesman_id'].'">'.$rs['company_name'].'</a></td></tr>';
			break;
			
			case "cancelled";
			echo '<tr><td align="left"><a href="edit_job.php?id='.$rs['id'].'">'.$rs['title'].'</a></td><td><a href="edit_user.php?id='.$rs['user_id'].'">'.$rs['firstname'].' '.$rs['surname'].'</a></td><td>'.$rs['trade'].'</td><td>'.date("d/m/Y", strtotime($rs['date_added'])).'</td><td>'.$rs['reason'].'</td></tr>';
			break;
			
			case "deleted";
			echo '<tr><td align="left"><a href="edit_job.php?id='.$rs['id'].'">'.$rs['title'].'</a></td><td><a href="edit_user.php?id='.$rs['user_id'].'">'.$rs['firstname'].' '.$rs['surname'].'</a></td><td>'.$rs['trade'].'</td><td>'.date("d/m/Y", strtotime($rs['date_added'])).'</td><td><a href="?id='.$rs['id'].'&amp;action=activate_job">Approve</a></td><td><a onclick="confirm_ban(\'?ban='.$rs['user_id'].'&amp;job_id='.$rs['id'].'\',\'this user\');" title="Ban"><img src="images/na.png" alt="Ban" /></a></td></tr>';
			break;
			
			default:
			echo '<tr><td align="left"><a href="edit_job.php?id='.$rs['id'].'">'.$rs['title'].'</a></td><td><a href="edit_user.php?id='.$rs['user_id'].'">'.$rs['firstname'].' '.$rs['surname'].'</a></td><td>'.$rs['trade'].'</td><td>'.date("d/m/Y", strtotime($rs['date_added'])).'</td><td><a href="?id='.$rs['id'].'&amp;action=deactivate_job">Deactivate</a></td><td><a onclick="confirm_ban(\'?ban='.$rs['user_id'].'&amp;job_id='.$rs['id'].'\',\'this user\');" title="Ban"><img src="images/na.png" alt="Ban" /></a></td></tr>';
			break;
			
			}
		
		}
		
	echo '</tbody></table>';
	
	}
	
	else{
	
	echo '<p style="float: left; width: 100%; text-align: center;">There are currently no jobs for that search.</p>';
	
	}
	
	
	?>

	</div>

</div>
	
<?php
	require('includes/footer.php');
?>